/**
 * 
 */
package cn.salesuite.movie.db;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.util.Log;
import cn.salesuite.movie.domain.Cinema;
import cn.salesuite.movie.domain.City;

/**
 * 操作address.db的helper,封装了对城市信息的各种数据库操作
 * @author Tony Shen
 * 
 */
public class DatabaseHelper extends SQLiteOpenHelper {

	// The Android's default system path of your application database.
	private static String DB_PATH = "/data/data/databases/";
	private static String DB_NAME = "address.db";

	private static DatabaseHelper instance = null;

	private SQLiteDatabase m_database;
	private Context m_context;

	public static DatabaseHelper GetInstance(Context context) {
		if (instance == null)
			instance = new DatabaseHelper(context);

		return instance;
	}

	protected DatabaseHelper(Context context) {
		super(context, DB_NAME, null, 1);
		m_database = null;
		this.m_context = context;
		this.DB_PATH = "/data/data/" + context.getPackageName() + "/databases/";
	}

	/*
	 * 
	 * Creates a empty database on the system and rewrites it with your own
	 * database.
	 */
	public void createDataBase() throws IOException {

		boolean dbExist = checkDataBase();
		Log.i("dbExist","dbExist1="+dbExist);
		if (dbExist) {
			// do nothing - database already exist
		} else {
			// By calling this method and empty database will be created into
			// the default system path
			// of your application so we are gonna be able to overwrite that
			// database with our database.
			this.getReadableDatabase();

			try {
				copyDataBase();
			} catch (IOException e) {
				throw new Error("Error copying database");
			}
		}

	}

	/**
	 * Check if the database already exist to avoid re-copying the file each
	 * time you open the application.
	 * 
	 * @return true if it exists, false if it doesn't
	 */
	private boolean checkDataBase() {

		SQLiteDatabase checkDB = null;

		try {
			String myPath = DB_PATH + DB_NAME;
			checkDB = SQLiteDatabase.openDatabase(myPath, null,
					SQLiteDatabase.OPEN_READONLY);

		} catch (SQLiteException e) {

			// database does't exist yet.

		}

		if (checkDB != null) {

			checkDB.close();

		}

		return checkDB != null ? true : false;
	}

	/**
	 * Copies your database from your local assets-folder to the just created
	 * empty database in the system folder, from where it can be accessed and
	 * handled. This is done by transfering bytestream.
	 * */
	private void copyDataBase() throws IOException {

		// Open your local db as the input stream 6  
		InputStream myInput = m_context.getAssets().open(DB_NAME);

		// Path to the just created empty db
		String outFileName = DB_PATH + DB_NAME;

		// Open the empty db as the output stream
		OutputStream myOutput = new FileOutputStream(outFileName);

		// transfer bytes from the inputfile to the outputfile
		byte[] buffer = new byte[1024];
		int length;
		while ((length = myInput.read(buffer)) > 0) {
			myOutput.write(buffer, 0, length);
		}

		// Close the streams
		myOutput.flush();
		myOutput.close();
		myInput.close();

	}

	public void openDataBase() throws SQLiteException {		
		if (!checkDataBase()) {
			this.DB_PATH = Environment.getExternalStorageDirectory() + "/salesuite/";
			try {
				copyDataBase();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		
		// Open the database
		String myPath = DB_PATH + DB_NAME;
		m_database = SQLiteDatabase.openDatabase(myPath, null,
				SQLiteDatabase.OPEN_READWRITE);
		if (m_database != null)
			m_database.setLockingEnabled(false);
	}
	
	
	public void save(City obj) {
		String sql="insert into city(" +
		"citycode,cityname,shortname,provincename" +
		")values(?,?,?,?)";
		Object[] bindArgs={obj.cityCode,obj.cityName,obj.shortName,obj.provinceName};
		m_database.execSQL(sql,bindArgs);
		
	}
	
	public String[] getProvinces(){
		String[] provinces=null;
		String selection="id > ?";
		String[] selectionArgs={"0"};
		Cursor cursor=m_database.query("city", new String[]{"provincename"},selection, selectionArgs, "provincename", null, "id");
		provinces=new String[cursor.getCount()];
		int i=0;
		while(cursor.moveToNext()){
			provinces[i]= cursor.getString(0);
			i++;
		}
		cursor.close();

		return provinces;
	}
	
	public City getCity(String cityname) {
		String selection="cityname =?";
		String[] selectionArgs={""+cityname+""};
		Cursor cursor=m_database.query("city", null,selection, selectionArgs, null, null, null);
		City obj=null;
		if(cursor.moveToNext()){
			obj= new City();
			obj.cityCode=cursor.getString(1);
			obj.cityName=cursor.getString(2);
			obj.shortName=cursor.getString(3);
			obj.provinceName=cursor.getString(4);		
		}
		cursor.close();

		return obj;
	}
	
	public List<City>  getCityList(String provincename) {
		String selection="provincename like ?";
		String[] selectionArgs={"%"+provincename+"%"};
		Cursor cursor=m_database.query("city", null,selection, selectionArgs, null, null, null);
		List<City> list=new ArrayList<City>();
		City obj=null;
		while(cursor.moveToNext()){
			obj= new City();
			obj.cityCode=cursor.getString(1);
			obj.cityName=cursor.getString(2);
			obj.shortName=cursor.getString(3);
			obj.provinceName=cursor.getString(4);		
			list.add(obj);
		}
		cursor.close();

		return list;
	}
	
	public List<City>  getCityLike(String keywords) {
		String selection="cityname like ? or provincename like ? or shortname like ?";
		String[] selectionArgs={"%"+keywords+"%","%"+keywords+"%","%"+keywords+"%"};
		Cursor cursor=m_database.query("city", null,selection, selectionArgs, null, null, null);
		List<City> list=new ArrayList<City>();
		City obj=null;
		while(cursor.moveToNext()){
			obj= new City();
			obj.cityCode=cursor.getString(1);
			obj.cityName=cursor.getString(2);
			obj.shortName=cursor.getString(3);
			obj.provinceName=cursor.getString(4);		
			list.add(obj);
		}
		cursor.close();

		return list;
	}
	
	public void deleteAllCity() {
		String sql="delete from city";
		m_database.execSQL(sql);
	}
	
	/**
	 * 根据citycode获取城市的电影院列表
	 * @param citycode
	 * @return
	 */
	public List<Cinema> getCinemaList(String citycode) {
		String selection="citycode like ?";
		String[] selectionArgs={"%"+citycode+"%"};
		Cursor cursor=m_database.query("cinema", null,selection, selectionArgs, null, null, null);
		List<Cinema> list=new ArrayList<Cinema>();
		Cinema obj=null;
		while(cursor.moveToNext()){
			obj= new Cinema();
			obj.provinceName=cursor.getString(1);
			obj.cityName=cursor.getString(2);
			obj.cityCode=cursor.getString(3);
			obj.citySubdivision=cursor.getString(4);	
			obj.cinemaName=cursor.getString(5);
			obj.cinameAddress=cursor.getString(6);
			obj.redCard=cursor.getString(7);
			obj.greenCard=cursor.getString(8);
			obj.couponType=cursor.getString(9);
			obj.cinemaLongitude=cursor.getString(10);
			obj.cinemaLatitude=cursor.getString(11);
			obj.telephone=cursor.getString(12);
			obj.kangouCinemaId=cursor.getString(13);
			list.add(obj);
		}
		cursor.close();

		return list;
	}
	
	/**
	 * 根据citycode获取城市的电影院id列表(看购的id)
	 * @param citycode
	 * @return
	 */
	public List<String> getKangouIdList(String citycode) {
		String selection="citycode like ?";
		String[] selectionArgs={"%"+citycode+"%"};
		Cursor cursor=m_database.query("cinema", null,selection, selectionArgs, null, null, null);
		List<String> list=new ArrayList<String>();
		while(cursor.moveToNext()){
			list.add(cursor.getString(13));
		}
		cursor.close();

		return list;
	}
	
	/**
	 * 根据城市的citycode和行政区名，获取该区所有的电影院
	 * @param citycode
	 * @param citySubdivision
	 * @return
	 */
	public List<Cinema> getCinemaList(String citycode,String citySubdivision) {
		String selection="citycode like ? and city_subdivision like ?";
		String[] selectionArgs={"%"+citycode+"%","%"+citySubdivision+"%"};
		
		Cursor cursor=m_database.query("cinema", null,selection, selectionArgs, null, null, null);
		List<Cinema> list=new ArrayList<Cinema>();
		Cinema obj=null;
		while(cursor.moveToNext()){
			obj= new Cinema();
			obj.provinceName=cursor.getString(1);
			obj.cityName=cursor.getString(2);
			obj.cityCode=cursor.getString(3);
			obj.citySubdivision=cursor.getString(4);	
			obj.cinemaName=cursor.getString(5);
			obj.cinameAddress=cursor.getString(6);
			obj.redCard=cursor.getString(7);
			obj.greenCard=cursor.getString(8);
			obj.couponType=cursor.getString(9);
			obj.cinemaLongitude=cursor.getString(10);
			obj.cinemaLatitude=cursor.getString(11);
			obj.telephone=cursor.getString(12);
			obj.kangouCinemaId=cursor.getString(13);
			list.add(obj);
		}
		cursor.close();

		return list;
	}
	
	/**
	 * 根据城市的citycode和优惠券类型，获取该城市所有可用该优惠券的电影院列表
	 * @param citycode
	 * @param citySubdivision
	 * @return
	 */
	public List<Cinema> getCinemaListByCoupon(String citycode,String couponType) {
		String selection="citycode like ? and COUPON_TYPE like ?";
		String[] selectionArgs={"%"+citycode+"%","%"+couponType+"%"};
		
		Cursor cursor=m_database.query("cinema", null,selection, selectionArgs, null, null, null);
		List<Cinema> list=new ArrayList<Cinema>();
		Cinema obj=null;
		while(cursor.moveToNext()){
			obj= new Cinema();
			obj.provinceName=cursor.getString(1);
			obj.cityName=cursor.getString(2);
			obj.cityCode=cursor.getString(3);
			obj.citySubdivision=cursor.getString(4);	
			obj.cinemaName=cursor.getString(5);
			obj.cinameAddress=cursor.getString(6);
			obj.redCard=cursor.getString(7);
			obj.greenCard=cursor.getString(8);
			obj.couponType=cursor.getString(9);
			obj.cinemaLongitude=cursor.getString(10);
			obj.cinemaLatitude=cursor.getString(11);
			obj.telephone=cursor.getString(12);
			obj.kangouCinemaId=cursor.getString(13);
			list.add(obj);
		}
		cursor.close();

		return list;
	}
	
	/**
	 * 获取上映某部影片的电影院列表(当前城市的电影院)
	 * @param playList
	 * @return
	 */
	public List<Cinema> getCinemaList(List<String> playList) {
		List<Cinema> list=new ArrayList<Cinema>();
		
		String selection="KANGOU_CINEMAID like ?";
		for(String kangouCinemaId:playList) {
			String[] selectionArgs={"%"+kangouCinemaId+"%"};
			Cursor cursor=m_database.query("cinema", null,selection, selectionArgs, null, null, null);
			Cinema obj=null;
			while(cursor.moveToNext()){
				obj= new Cinema();
				obj.provinceName=cursor.getString(1);
				obj.cityName=cursor.getString(2);
				obj.cityCode=cursor.getString(3);
				obj.citySubdivision=cursor.getString(4);	
				obj.cinemaName=cursor.getString(5);
				obj.cinameAddress=cursor.getString(6);
				obj.redCard=cursor.getString(7);
				obj.greenCard=cursor.getString(8);
				obj.couponType=cursor.getString(9);
				obj.cinemaLongitude=cursor.getString(10);
				obj.cinemaLatitude=cursor.getString(11);
				obj.telephone=cursor.getString(12);
				obj.kangouCinemaId=cursor.getString(13);
				list.add(obj);
			}
			cursor.close();
		}
		
		return list;		
	}
	
	/**
	 * 根据城市的citycode返回该城市的行政区
	 * @param citycode
	 * @return
	 */
	public String[] getcitySubdivisions(String citycode) {
		String[] citySubdivisions=null;
		String selection="citycode =?";
		String[] selectionArgs={""+citycode+""};
		Cursor cursor=m_database.query("cinema", new String[]{"city_subdivision"},selection, selectionArgs, "city_subdivision", null, null);
		citySubdivisions=new String[cursor.getCount()];
		int i=0;
		while(cursor.moveToNext()){
			citySubdivisions[i]= cursor.getString(0);
			i++;
		}
		cursor.close();
		
		return citySubdivisions;
	}
	
	/**
	 * 根据影院名称获取 cinema对象
	 * @param cinemaName
	 * @return
	 */
	public Cinema getCinema(String cinemaName) {
		String selection="cinema_name =?";
		String[] selectionArgs={""+cinemaName+""};
		Cursor cursor=m_database.query("cinema", null,selection, selectionArgs, null, null, null);
		Cinema obj=null;
		if(cursor.moveToNext()){
			obj= new Cinema();
			obj.provinceName=cursor.getString(1);
			obj.cityName=cursor.getString(2);
			obj.cityCode=cursor.getString(3);
			obj.citySubdivision=cursor.getString(4);	
			obj.cinemaName=cursor.getString(5);
			obj.cinameAddress=cursor.getString(6);
			obj.redCard=cursor.getString(7);
			obj.greenCard=cursor.getString(8);
			obj.couponType=cursor.getString(9);
			obj.cinemaLongitude=cursor.getString(10);
			obj.cinemaLatitude=cursor.getString(11);
			obj.telephone=cursor.getString(12);
			obj.kangouCinemaId=cursor.getString(13);
		}
		cursor.close();

		return obj;
	}
	
	/**
	 * 根据城市名 返回该城市中心的经纬度
	 * @param cityName
	 * @return
	 */
	public String getCityPosition(String cityName) {
		String selection="city_name_ch like ?";
		String[] selectionArgs={"%"+cityName+"%"};
		Cursor cursor=m_database.query("CHINA_CITY_POSITION", null,selection, selectionArgs, null, null, null);
		String position = null;
		if(cursor.moveToNext()){
			position=cursor.getString(2);	
		}
		cursor.close();
		
		return position;
	}
	
	//------------------------------------------------------------------

	public void delete(Integer id) {		
	}

	public City find(Integer id) {
		return null;
	}

	public int getCount() {
		return 0;
	}

	public List<City> getData(long startIndex, long maxCount, String order) {
		return null;
	}

	public void update(City obj) {
		
	}

	public void closeDatabase() {
		super.close();
		m_database.close();
		close();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}

	@Override
	public void onOpen(SQLiteDatabase db) {
		super.onOpen(db);
	}
}
